Exporting a Text String to Excel as a Hyperlink in a Cell

Am fairly new to DXL, but I have managed to figure out how to export DOORS data to Excel cells through DXL, but I have been tasked with exporting text and storing it in a cell as an excel hyperlink. I have not seen any code snippets even close to this. Any suggestions/code?

Thanks,
DavidBrian - Mon Jun 20 19:17:31 EDT 2011

Re: Exporting a Text String to Excel as a Hyperlink in a Cell
Mathias Mamsch - Tue Jun 21 03:31:22 EDT 2011

You can use the HYPERLINK function of Excel with any export code.
 

OleAutoObj wbs 
OleAutoObj wb
OleAutoObj ws
OleAutoObj objCell
OleAutoArgs args = create
 
void openExcel() {
   OleAutoObj ex = oleCreateAutoObject "Excel.Application"
   oleGet (ex, "Workbooks", wbs) 
   oleMethod (wbs, "Add", args, wb)
   olePut(ex, "Visible", true)
   put(args, 1)
   oleGet (ex, "Worksheets", args, ws) 
} 
 
void pasteString (int y, int x, string s) {
                clear args
                        put (args, y)
                        put (args, x)
                        oleGet (ws, "Cells", args, objCell) // select cell
 
                        clear args
                        put (args, objCell)
                        copyToClipboard s
                        print oleMethod (ws, "Paste",args)
}
 
openExcel() 
pasteString (3,3, "=HYPERLINK(\"http://www.it-qbase.de\",\"Click to go to IT-QBase\")")

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

Re: Exporting a Text String to Excel as a Hyperlink in a Cell
DavidBrian - Tue Jun 21 13:02:26 EDT 2011

Thanks very much for the simple solution. Using an excel function had not occurred to me. I think I was over-thinking the problem and assumed I would have to get a handle to some kind of hyperlink object or property of the cell.

This solution was interesting because using a function as this does gives slightly different results than using the manual Excel->Insert->Hyperlink method. The actual cell value is different, of course, and you have to add your own formatting (blue and underlined), which is simple enough.
 

OleAutoObj objFont
oleGet(objCell, "Font", objFont)
olePut(objFont, "ColorIndex", 5)
const int xlUnderlineStyleSingle = 2
olePut(objFont, "Underline", xlUnderlineStyleSingle)

Re: Exporting a Text String to Excel as a Hyperlink in a Cell
oaklodge - Thu Jan 21 07:58:24 EST 2016

OleAutoObj wbs 
OleAutoObj wb
OleAutoObj ws
OleAutoObj objCell
OleAutoArgs args = create
 
void openExcel() {
   OleAutoObj ex = oleCreateAutoObject "Excel.Application"
   oleGet (ex, "Workbooks", wbs) 
   oleMethod (wbs, "Add", args, wb)
   olePut(ex, "Visible", true)
   put(args, 1)
   oleGet (ex, "Worksheets", args, ws) 
} 
 
void pasteString (int y, int x, string s) {
                clear args
                        put (args, y)
                        put (args, x)
                        oleGet (ws, "Cells", args, objCell) // select cell
 
                        clear args
                        put (args, objCell)
                        copyToClipboard s
                        print oleMethod (ws, "Paste",args)
}
 
openExcel() 
pasteString (3,3, "=HYPERLINK(\"http://www.it-qbase.de\",\"Click to go to IT-QBase\")")

Thanks Matthias. Also want to say that this works for DOORS URLs as well. And works if you output this to CSV and open in Excel.

string url = "=HYPERLINK(\"" (getURL obj) "\",\"" (identifier obj) "\")"